package project.ccso.top.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import project.ccso.top.dao.SchoolDao;
import project.ccso.top.entity.City;
import project.ccso.top.entity.School;
import project.ccso.top.util.DBUtil;
import project.ccso.top.util.DateUtil;

/**
 * 高校Dao的实现类
 * @author aibinxiao
 * @date 2018年8月23日 上午10:42:44
 */
public class SchoolDaoImpl implements SchoolDao {
	// 日志
	private static Logger logger = LoggerFactory.getLogger(SchoolDaoImpl.class);
	
	// DBUtil工具
	private DBUtil dbUtil = new DBUtil();
	
	// 1.根据ciytId获取所有的高校Dao的实现
	@Override
	public List<School> selectByCityId(int cityId) {
		List<School> list = new ArrayList<>();
		String sql = "select * from t_school where sch_city_id = ?";
		ResultSet rs = dbUtil.executeQuery(sql, new Object[] {cityId});
		try {
			while(rs.next()) {
				list.add(new School(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getInt(5), rs.getString(6), rs.getString(7)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int countSchByParams(String schoolId, String schoolName, String provinceId, String cityId, String startDate,
			String endDate) {
		List<Object> objects = new ArrayList<>();
		int count = 0;
		String sql = "select count(*) from t_school where 1=1";
		if (!StringUtils.isEmpty(schoolId)) {
			sql += " and sch_id = ?";
			objects.add(schoolId);
		}
		if (!StringUtils.isEmpty(schoolName)) {
			sql += " and sch_name = ?";
			objects.add(schoolName);
		}
		if (!StringUtils.isEmpty(provinceId)) {
			sql += " and sch_province_id = ?";
			objects.add(provinceId);
		}
		if (!StringUtils.isEmpty(cityId)) {
			sql += " and sch_city_id = ?";
			objects.add(cityId);
		}
		if (!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
			startDate += " 00:00:00";
			endDate += " 23:59:59";
			sql += " and sch_create_time between ? and ?";
		}
		ResultSet rs = dbUtil.executeQuery(sql, objects);
		try {
			while (rs.next()) {
				count = rs.getInt(1);
			}
			logger.info("找到(" + count + ")个符合查询条件的城市信息");
			return count;
		} catch (SQLException e) {
			logger.info("没有找到符合查询条件求的城市信息");
			e.printStackTrace();
			return count;
		}
	}

	@Override
	public List<School> findSchPageByParams(String schoolId, String schoolName, String provinceId, String cityId,
			String startDate, String endDate, int startIndex, int records) {
		List<School> result = new ArrayList<>();
		List<Object> objects = new ArrayList<>();
		String sql = "select count(*) from t_school where 1=1";
		if (!StringUtils.isEmpty(schoolId)) {
			sql += " and sch_id = ?";
			objects.add(schoolId);
		}
		if (!StringUtils.isEmpty(schoolName)) {
			sql += " and sch_name = ?";
			objects.add(schoolName);
		}
		if (!StringUtils.isEmpty(provinceId)) {
			sql += " and sch_province_id = ?";
			objects.add(provinceId);
		}
		if (!StringUtils.isEmpty(cityId)) {
			sql += " and sch_city_id = ?";
			objects.add(cityId);
		}
		if (!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) {
			startDate += " 00:00:00";
			endDate += " 23:59:59";
			sql += " and sch_create_time between ? and ?";
		}
		sql += " limit ? , ?";
		objects.add(startIndex);
		objects.add(records);
		ResultSet rs = dbUtil.executeQuery(sql, objects);
		try {
			while (rs.next()) {
				School school = new School(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4),rs.getInt(5), rs.getString(6), rs.getString(7));
				result.add(school);
			}
		} catch (SQLException e) {
			result = null;
		}
		return result;
	}

	/*// 1.根据城市Id获取所有的高校Dao实现类
	@Override
	public List<School> findCitysByProId(Integer cityId) {
		String hql = "from School where cityId = ? and status = ?";
		List<School> result = null;
		List<School> list = this.getHibernateTemplate().find(hql, cityId, 0);
		if(list != null && list.size() > 0){
			logger.info("所有高校信息集合：" + list);
			result = list;
		}
		return result;
	}

	// 2.根据高校id获取高校信息Dao实现类
	@Override
	public School findSchoolById(Integer schoolId) {
		String hql = "from School where id = ?";
		School result = null;
		List<School> list = this.getHibernateTemplate().find(hql, schoolId);
		if(list != null && list.size() > 0){
			logger.info("根据高校id获取高校信息：" + list.get(0));
			result = list.get(0);
		}
		return result;
	}

	// 3.查询符合条件的高校记录条数Dao实现类
	@Override
	public int countByParams(String id, String name, String startDate, String endDate, String provinceIdStr,
			String cityIdStr) {
		int count = 0;
		// 使用离线条件查询.
		DetachedCriteria criteria = DetachedCriteria.forClass(School.class);
		if(!StringUtils.isEmpty(provinceIdStr)){
			Integer provinceId = Integer.valueOf(provinceIdStr);
			criteria.add(Restrictions.eq("provinceId", provinceId));
		}
		if(!StringUtils.isEmpty(cityIdStr)){
			Integer cityId = Integer.valueOf(cityIdStr);
			criteria.add(Restrictions.eq("cityId", cityId));
		}
		if(!StringUtils.isEmpty(id)){
			Integer threeCategoryId = Integer.valueOf(id);
			criteria.add(Restrictions.idEq(threeCategoryId));
		}
		if(!StringUtils.isEmpty(name)){
			criteria.add(Restrictions.like("name", name, MatchMode.ANYWHERE));
		}
		if(!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)){
			startDate += " 00:00:00";
			endDate += " 23:59:59";
			criteria.add(Restrictions.between("createTime", startDate, endDate));
		}
		// 倒序排序输出:
		criteria.addOrder(Order.asc("id"));
		// 执行查询:
		List<School> list = this.getHibernateTemplate().findByCriteria(criteria);
		if (list != null && list.size() > 0) {
			count = list.size();
			logger.info("找到("+ list.size() +")个符合查询条件的高校");
		} else{
			logger.info("没有找到符合查询条件求的高校");
		}
		return count;
	}

	// 4.查询符合条件的高校记录集合Dao实现类
	@Override
	public List<School> findPageByParams(String id, String name, String startDate, String endDate, String provinceIdStr,
			String cityIdStr, int startIndex, int records) {
		List<School> result = null;
		// 使用离线条件查询.
		DetachedCriteria criteria = DetachedCriteria.forClass(School.class);
		if(!StringUtils.isEmpty(provinceIdStr)){
			Integer provinceId = Integer.valueOf(provinceIdStr);
			criteria.add(Restrictions.eq("provinceId", provinceId));
		}
		if(!StringUtils.isEmpty(cityIdStr)){
			Integer cityId = Integer.valueOf(cityIdStr);
			criteria.add(Restrictions.eq("cityId", cityId));
		}
		if(!StringUtils.isEmpty(id)){
			Integer threeCategoryId = Integer.valueOf(id);
			criteria.add(Restrictions.idEq(threeCategoryId));
		}
		if(!StringUtils.isEmpty(name)){
			criteria.add(Restrictions.like("name", name, MatchMode.ANYWHERE));
		}
		if(!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)){
			startDate += " 00:00:00";
			endDate += " 23:59:59";
			criteria.add(Restrictions.between("createTime", startDate, endDate));
		}
		// 倒序排序输出:
		criteria.addOrder(Order.asc("id"));
		// 执行查询:
		List<School> list = this.getHibernateTemplate().findByCriteria(criteria, startIndex, records);
		if (list != null && list.size() > 0) {
			result = list;
			logger.info("找到("+ result.size() +")个符合查询条件的高校");
		} else{
			logger.info("没有找到符合查询条件求的高校");
		}
		return result;
	}
	
	// 5.更新或者保存高校信息Dao实现类
	@Override
	public void saveSchool(School school) throws Exception{
		this.getHibernateTemplate().saveOrUpdate(school);
	}

	// 6.更新高校的启用状态Dao实现类
	@Override
	public void updateStauts(Integer id, Integer statusCode) throws Exception{
		String hql = "from School where id = ?";
		List<School> list = this.getHibernateTemplate().find(hql, id);
		if(list!=null && list.size()>0){
			School school = list.get(0);
			school.setStatus(statusCode);
			school.setAlterTime(DateUtils.DateToStr(new Date()));
			this.getHibernateTemplate().saveOrUpdate(school);
		}
	}*/
}
